package gri_database;

use DBI;
use Data::Dumper;
use File::Basename;
use MIME::Base64;

#########################################################################
# Class level variables to record errors of last call.			#
#########################################################################

my $cldb_errcode=0;
my $cldb_errstr=undef;

sub _exists($$$) {
my $self=shift;
my ($type,$obj)=@_;
my ($cmd,$rc,$sth);

	$cldb_errcode=0;
	$cldb_errstr=undef;
	$type="table" if !defined($type);
	$cmd="SELECT * FROM SQLITE_MASTER WHERE type = '$type' AND name = '$obj';";
	my $h=$self->{dbh}->selectrow_hashref("$cmd");
	if(!defined($h)) {
		return 0;
	}
	if(!defined($h)) {
		return 0;
	}
	return 1;
}

sub qs($$) {
my $self=shift;
my $s=shift;

	return $self->{dbh}->quote($s);
}

sub get_dbh($) {
	return $_[0]->{dbh};
}

sub do($$;$) {
my $self=shift;
my $timeout=$_[2];

	$timeout=3 if !defined($timeout);
	$cldb_errcode=0;
	$cldb_errstr=undef;
	my $ctime=0;
	while($ctime<=$timeout) {
		$self->{dbh}->do($_[0]);
		if(defined($self->{dbh}->err) && $self->{dbh}->err==5) {
			$cldb_errcode=5;
			$cldb_errstr=$self->{dbh}->errstr;
			# locked error;
			$ctime+=0.10;
			select(undef,undef,undef,0.10);
			next;
		}
		my $rc=$self->{dbh}->errstr;
		if(defined($rc)) {
			$cldb_errcode=2;
			$cldb_errstr=$rc;
			return 0;
		}
		return 1;
	}
	return 0;
}

sub get_rows($$) {
my ($self,$query)=@_;
my ($sth,$rc,@R,$aref);

	$cldb_errcode=0;
	$cldb_errstr=undef;
	$sth=$self->{dbh}->prepare($query);
	if(!defined($sth)) {
		$rc=$self->{dbh}->errstr;
		$cldb_errcode=5;
		$cldb_errstr=$rc;
		return undef;
	}
	$sth->execute();
	@R=();
	while($aref  = $sth->fetchrow_hashref) {
		push @R,{%$aref};
	}
	return \@R;
}

sub new {
my $proto = shift;
my $class = ref($proto) || $proto;
my $self  = {};
my $dbname=$_[0];

	$self->{dbname}=$dbname;
	my $dir=dirname($dbname);
	if(! -w $dir) {
		print STDERR "ERROR: The directory '$dir' is not writable by UID '$<'.\n";
		return undef;

	}
	$self->{dbh}=DBI->connect("dbi:SQLite:dbname=$dbname","","",
		{RaiseError => 0,PrintError => 0,AutoCommit => 1});

	
	bless($self,$class);

	# Create the doc_index table if necessary
	if(! _exists($self,"table","doc_index")) {
		my $r=$self->do("CREATE TABLE doc_index(description,original_name,local_name,content_sha1,uploaded_time,sections)");
		if(!$r) {
			return undef;
		}
	} else {
		my $dbh=$self->get_dbh();
		my $rr=_add_field($dbh,"doc_index","uploader","CHAR(20)","");
	}

	# Create the version_doc_index table if it does not exist
	if(! _exists($self,"table","version_doc_index")) {
		my $r=$self->do("CREATE TABLE version_doc_index(original_name,clocal_name,local_name,olocal_name,content_sha1,uploaded_time,version)");
		if(!$r) {
			return undef;
		}
	}

	# Create the sections table if necessary
	if(! _exists($self,"table","sections")) {
		my $r=$self->do("CREATE TABLE sections(name,parent,description,title,reader_groups,publisher_groups,attributes)");
		if(!$r) {
			return undef;
		}
	} else {
		my $dbh=$self->get_dbh();
		my $rr=_add_field($dbh,"sections","attributes","VARCHAR(255)","");
	}

	# Create the group_info table if necessary
	if(! _exists($self,"table","group_info")) {
		my $r=$self->do("CREATE TABLE group_info(name,description,users)");
		if(!$r) {
			return undef;
		}
	}
	return $self;
}

sub error {
	return($cldb_errcode,$cldb_errstr);
}

sub get_user_groups {
my $self=shift;
my %args=@_;

	if(!exists($args{user})) {
		return (-2,[]);
	}
	my $user=$args{user};

	my %groups=();
	my $res=$self->get_rows("SELECT * FROM group_info;");
	if(! @$res) {
	
		return(1,[]);
	}
	for my $rec (@$res) {
		next if !defined($rec->{users});
		my @x=split(/,/,$rec->{users});
		if(grep {$_ eq $user} @x) {
			$groups{$rec->{name}}=1;
		}
	}

	my @d=sort(keys(%groups));
	return(1,[@d]);
}

sub is_user_in_group {
my $self=shift;
my %args=@_;

	if(!exists($args{user})) {
		return -2;
	}
	if(!exists($args{group})) {
		return -2;
	}
	my $user=$args{user};
	my $group=$args{group};

	my $res=$self->get_rows("SELECT * FROM group_info where name = '$group';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{users});
	if(grep {$_ eq $user} @x) {
		return 1;
	}
	return 0;
}

sub add_section {
my $self=shift;
my %args=@_;

	if(!exists($args{section}) || !exists($args{parent})) {
		return -2;
	}
	if(!exists($args{description}) || !exists($args{title})) {
		return -2;
	}
	my $section=$args{section};
	my $parent=$args{parent};
	my $description=$args{description};
	my $title=$args{title};
	my $attributes="";
	$attributes=$args{attributes} if exists($args{attributes});

	if($section =~ /\s/ || $parent =~ /\s/) {
		return -5;
	}

	#################################################################
	# Make sure the section does not exist yet, abort if so.	#
	#################################################################
	my $res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(@$res) {
		return -1;
	}

	#################################################################
	# If the parent has been specified make sure it exists 		#
	# already and abort if not.					#
	#################################################################

	if(length($parent)) {
		$res=$self->get_rows("SELECT * FROM sections WHERE name = '$parent';");
		if(! @$res) {
			return -4;
		}
	}

	#################################################################
	# Insert the blank record into the database [no users]		#
	#################################################################

	my $r=$self->do("INSERT INTO sections (name,parent,description,title,reader_groups,publisher_groups,attributes) VALUES ('$section','$parent','$description','$title','','','$attributes');");
	if(!$r) {
		return -3;
	}
	return 1;
}

sub add_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{description})) {
		return -2;
	}
	my $group=$args{group};
	my $description=$args{description};
	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(@$res) {
		return -1;
	}
	my $r=$self->do("INSERT INTO group_info (name,description,users) VALUES ('$group','$description','');");
	if(!$r) {
		return -3;
	}
	return 1;
}

sub remove_section {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return -2;
	}
	my $section=$args{section};
	my $res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		return -1;
	}
	my $parent=$res->[0]->{parent};
	my $r=$self->do("DELETE FROM sections WHERE name = '$section';");
	if(!$r) {
		return -3;
	}

	#################################################################
	# Once we have deleted the section, look for sections where 	#
	# it was the parent and move them parents to the one above.	#
	#################################################################
	$r=$self->do("UPDATE sections SET parent = '$parent' WHERE parent = '$section';");
	if(!$r) {
		return -3;
	}

	return 1;
}

# Deletion of a group is actually two different bits ... delete references
# to the group from all publisher_groups and reader_groups entries in the
# 'sections' table, and then actually delete the group itself from the
# 'group_info' table.

sub remove_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	my $group=$args{group};

	my @updates=();
	my $res2=$self->get_rows("SELECT * FROM sections;");
	for my $cres (@$res2) {
		next if !defined($cres->{name});

		my $n=$cres->{name};
		my $readers=$cres->{reader_groups};
		my $publishers=$cres->{publisher_groups};
		$readers="" if !defined($readers);
		$publishers="" if !defined($publishers);

		my $updated=0;
		my @F=split(/,/,$readers);
		if(grep {$_ eq $group} @readers) {
			$updated=1;
			$readers=join(",",grep {$_ ne $group} @readers);
		}
		if(grep {$_ eq $group} @publishers) {
			$updated=1;
			$publishers=join(",",grep {$_ ne $group} @publishers);
		}
		if($updated) {
			push @updates,"UPDATE sections SET reader_groups = '$readers', publisher_groups = '$publishers' WHERE name = '$n';";
		}
	}

	# Ok, if @updates>0 then we perform the updates now...		

	if(@updates) {
		for my $cupdate (@updates) {
			my $r=$self->do($cupdate);
			if(!$r) {
				return -3;
			}
		}
	}

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	my $r=$self->do("DELETE FROM group_info WHERE name = '$group';");
	if(!$r) {
		return -3;
	}
	return 1;
}

sub modify_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return (-2,"Missing mandatory 'group' argument");
	}
	my %updates=();
	for my $cfield ("description", "users") {
		if(exists($args{$cfield})) {
			my $v=$args{$cfield};
			$v="" if !defined($v);
			$updates{$cfield}=$args{$cfield};
		}
	}
	my $sql="UPDATE group_info SET ";
	if(scalar(keys %updates)==0) {
		return(-2,"No update fields supplied.");
	}
	my @V=();
	for my $ckey (keys %updates) {
		push @V,sprintf("$ckey = %s",qs($self,$updates{$ckey}));
	}
	$sql.=join(",",@V);
	$sql.=" WHERE name = '$args{group}';";

	my $res=$self->do($sql);
	if(!$res) {
		return (-1,"Unable to run update: $sql");
	}
	return(1,undef);
}

sub add_user_to_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{user})) {
		return -2;
	}
	my $group=$args{group};
	my $user=$args{user};

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{users});
	if(grep {$_ eq $user} @x) {
		return 1;
	}
	$y=join(",",(@x,$user));
	my $r=$self->do("UPDATE group_info SET users = '$y' WHERE name = '$group';");
	if(! $r) {
		return -3;
	}
	return 1;
}

sub remove_user_from_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{user})) {
		return -2;
	}
	my $group=$args{group};
	my $user=$args{user};

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{users});
	if(! grep {$_ eq $user} @x) {
		return 1;
	}
	my @y=grep {$_ ne $user} @x;
	my $y2=join(",",@y);
	my $r=$self->do("UPDATE group_info SET users = '$y' WHERE name = '$group';");
	if(! $r) {
		return -3;
	}
	return 1;
}

sub remove_group_from_reader_list {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{section})) {
		return -2;
	}
	my $group=$args{group};
	my $section=$args{section};

	#################################################################
	# Check to see if the group and section actually exist.		#
	#################################################################

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	$res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{reader_groups});
	if(! grep {$_ eq $group} @x) {
		return 1;
	}
	my @y=grep {$_ ne $group} @x;
	my $z=join(",",@y);
	my $r=$self->do("UPDATE sections SET reader_groups = '$z' WHERE name = '$section';");
	if(! $r) {
		return -3;
	}
	return 1;
}

sub add_group_to_reader_list {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{section})) {
		return -2;
	}
	my $group=$args{group};
	my $section=$args{section};

	$cldb_errcode=0;
	$cldb_errstr=undef;

	#################################################################
	# Check to see if the group and section actually exist.		#
	#################################################################

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		$cldb_errcode=-1;
		$cldb_errstr="Unable to run: SELECT * FROM group_info WHERE name = '$group'";
		return -1;
	}
	$res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		$cldb_errcode=-1;
		$cldb_errstr="Unable to run: SELECT * FROM sections WHERE name = '$section'";
		return -1;
	}
	my @x=split(/,/,$res->[0]->{reader_groups});
	if(grep {$_ eq $group} @x) {
		return 1;
	}
	my $z=join(",",(@x,$group));
	my $r=$self->do("UPDATE sections SET reader_groups = '$z' WHERE name = '$section';");
	if(! $r) {
		$cldb_errcode=-1;
		$cldb_errstr="Unable to run: UPDATE sections SET reader_groups = '$z' WHERE name = '$section'";
		return -3;
	}
	return 1;
}

sub remove_group_from_publisher_list {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{section})) {
		return -2;
	}
	my $group=$args{group};
	my $section=$args{section};

	#################################################################
	# Check to see if the group and section actually exist.		#
	#################################################################

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	$res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{publisher_groups});
	if(! grep {$_ eq $group} @x) {
		return 1;
	}
	my @y=grep {$_ ne $group} @x;
	my $z=join(",",@y);
	my $r=$self->do("UPDATE sections SET publisher_groups = '$z' WHERE name = '$section';");
	if(! $r) {
		return -3;
	}
	return 1;
}

sub add_group_to_publisher_list {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return -2;
	}
	if(!exists($args{section})) {
		return -2;
	}
	my $group=$args{group};
	my $section=$args{section};

	#################################################################
	# Check to see if the group and section actually exist.		#
	#################################################################

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return -1;
	}
	$res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		return -1;
	}
	my @x=split(/,/,$res->[0]->{publisher_groups});
	if(grep {$_ eq $group} @x) {
		return 1;
	}
	my $z=join(",",(@x,$group));
	my $r=$self->do("UPDATE sections SET publisher_groups = '$z' WHERE name = '$section';");
	if(! $r) {
		return -3;
	}
	return 1;
}

sub list_groups {
my $self=shift;

	$res=$self->get_rows("SELECT name FROM group_info;");
	if(! @$res) {
		return (1,[]);
	}
	my @F=();
	for my $crec (@$res) {
		push @F,$crec->{name};
	}
	return(1,[@F]);
}

sub list_sections {
my $self=shift;

	$res=$self->get_rows("SELECT name FROM sections;");
	if(! @$res) {
		return (1,[]);
	}
	my @F=();
	for my $crec (@$res) {
		push @F,$crec->{name};
	}
	return(1,[@F]);
}

sub get_group {
my $self=shift;
my %args=@_;

	if(!exists($args{group})) {
		return (-2,{});
	}

	my $group=$args{group};

	my $res=$self->get_rows("SELECT * FROM group_info WHERE name = '$group';");
	if(! @$res) {
		return (-1,{});
	}
	return(1,{%{$res->[0]}});
}

sub get_section {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return (-2,{});
	}
	my $section=$args{section};

	my $res=$self->get_rows("SELECT * FROM sections WHERE name = '$section';");
	if(! @$res) {
		return (-1,{});
	}
	return(1,{%{$res->[0]}});
}

sub modify_section {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return (-2,"Missing mandatory 'section' argument");
	}
	my %updates=();
	for my $cfield ("parent", "attributes", "title", "description", "reader_groups", "publisher_groups") {
		if(exists($args{$cfield})) {
			my $v=$args{$cfield};
			$v="" if !defined($v);
			$updates{$cfield}=$args{$cfield};
		}
	}
	my $sql="UPDATE sections SET ";
	if(scalar(keys %updates)==0) {
		return(-2,"No update fields supplied.");
	}
	my @V=();
	for my $ckey (keys %updates) {
		push @V,sprintf("$ckey = %s",qs($self,$updates{$ckey}));
	}
	$sql.=join(",",@V);
	$sql.=" WHERE name = '$args{section}';";

	my $res=$self->do($sql);
	if(!$res) {
		return (-1,"Unable to run update: $sql");
	}
	return(1,undef);
}

#########################################################################
# This returns a list of section names the specified user can publish	#
# documents to. It does so by making several queries ...		#
# 1. Scan all groups and return those which contain the specified user.	#
# 2. Scan all sections and publisher lists looking for those which 	#
#    contain one or more of the users groups.				#
# 3. Check sections attributes for public, readread, publicwrite as	#
#    appropriate.							#
#########################################################################

sub get_user_sections {
my $self=shift;
my %args=@_;

	if(!exists($args{user})) {
		return (-2,[]);
	}
	my $user=$args{user};
	if(!exists($args{type})) {
		return (-2,[]);
	}
	my $type=$args{type};

	# Scan all group records looking for user ...

	my $s="SELECT name FROM group_info WHERE users = '$user' OR users LIKE '%,$user,%' OR users LIKE '$user,%' or users LIKE '%,$user';";
	my $res=$self->get_rows($s);
	# if(! @$res) {
		# return (1,[]);
	# }

	# We now grab all the group names ...

	my %groups=();
	for my $crec (@$res) {
		$groups{$crec->{name}}=1;
	}

	# Load in details of ALL sections ...
	$res=$self->get_rows("SELECT * FROM sections");
	
	my %sections=();
	for my $crec (@$res) {
		my $attrs=$crec->{attributes};
		if(defined($attrs)) {
			$attrs =~ s/\s+//g;
			my @A=split(/,/,$attrs);
			if(grep {$_ eq "public"} @A) {
				$sections{$crec->{name}}=1;
			}
			if(lc($type) eq "publisher" && grep {$_ eq "publicwrite"} @A) {
				$sections{$crec->{name}}=1;
			}
			if(lc($type) eq "reader" && grep {$_ eq "publicread"} @A) {
				$sections{$crec->{name}}=1;
			}
		}
	}
	if(lc($type) eq "publisher") {
		for my $cgroup (keys %groups) {
			for my $crec (@$res) {
				my @g=split(/,/,$crec->{publisher_groups});
				if(grep {$cgroup eq $_} @g) {
					$sections{$crec->{name}}=1;
				}
			}
		}
	} else {
		for my $cgroup (keys %groups) {
			for my $crec (@$res) {
				my @g=split(/,/,$crec->{reader_groups});
				if(grep {$cgroup eq $_} @g) {
					$sections{$crec->{name}}=1;
				}
			}
		}
	}
	return(1,[keys %sections]);
}

sub get_history_details {
my $self=shift;
my %args=@_;

	if(!exists($args{lname})) {
		return (-2,"Missing 'lname; parameter.");
	}

	my $s="SELECT * FROM version_doc_index WHERE local_name = '$args{lname}';";
	my $res=$self->get_rows($s);
	if(! @$res || !defined($res->[0]->{olocal_name})) {
		return(0,{});
	}
	return(1,$res->[0]);
}

sub get_details {
my $self=shift;
my %args=@_;

	if(!exists($args{sha1})) {
		return (-2,{});
	}
	if(!exists($args{lname})) {
		return (-2,{});
	}
	my $sha1=$args{sha1};
	my $lname=$args{lname};

	my $res;
	if($args{sha1} eq "NONE") {
		$res=$self->get_rows("SELECT * FROM doc_index WHERE local_name = '$args{lname}';");
	} else {
		$res=$self->get_rows("SELECT * FROM doc_index WHERE content_sha1 = '$sha1' AND local_name = '$args{lname}';");
	}
	if(! @$res) {
		return(0,undef);
	}
	return(1,$res->[0]);
}

sub get_details_by_sha1 {
my $self=shift;
my %args=@_;

	if(!exists($args{sha1})) {
		return (-2,{});
	}
	my $sha1=$args{sha1};

	my $res;
	if(exists($args{lname})) {
		$res=$self->get_rows("SELECT * FROM doc_index WHERE content_sha1 = '$sha1' AND local_name = '$args{lname}';");
	} else {
		$res=$self->get_rows("SELECT * FROM doc_index WHERE content_sha1 = '$sha1';");
	}
	if(! @$res) {
		return(0,undef);
	}
	return(1,$res->[0]);
}


# Here we attempt to add the specified document that already exists
# with local_name to the sections specified.  Since the docuemtn
# already exists we can add a new entry, but share the existing localname!!

sub add_doc_to_sections {
my $self=shift;
my %args=@_;

	my $uploader="";
	if(exists($args{uploader})) {
		$uploader=$args{uploader};
	}
	if(!exists($args{local_name})) {
		return -2;
	}
	my $local_name=$args{local_name};
	if(!exists($args{sections})) {
		return -2;
	}
	my $sections=$args{sections};
	if(!exists($args{content_sha1})) {
		return -2;
	}
	my $content_sha1=$args{content_sha1};
	if(!exists($args{description})) {
		return -2;
	}
	my $description=encode_base64($args{description});
	$description =~ s/\n//g;
	if(!exists($args{original_name})) {
		return -2;
	}
	my $original_name=$args{original_name};
	if(!exists($args{docrepos})) {
		return -2;
	}
	my $docrepos=$args{docrepos};
	my $t=time;
	my $s="INSERT INTO doc_index (description,original_name,local_name,content_sha1,uploaded_time,sections,uploader) VALUES ";
	$s.="('$description','$original_name','$local_name','$content_sha1','$t','$sections','$uploader');";
	my $r=$self->do($s);
	if(! $r) {
		return -3;
	}
	return 1;
}

sub add_new_document {
my $self=shift;
my %args=@_;

	my $uploader="";
	if(exists($args{uploader})) {
		$uploader=$args{uploader};
	}
	my @W=qw(content_sha1 sections description original_name docrepos temporary_file);
	for my $i (@W) {
		if(!exists($args{$i}) || !defined($args{$i})) {
			return -2;
		}
	}
	my $s="SELECT max(local_name) AS mm FROM doc_index";
	$res=$self->get_rows($s);
	my $mm;
	if(! @$res || !defined($res->[0]->{mm})) {
		$mm=1;
	} else {
		$mm=$res->[0]->{mm};
		$mm =~ s/^0+//;
		$mm=int($mm)+1;
	}
	my $local_name=sprintf("%06d",$mm);
	my $description=encode_base64($args{description});
	$description =~ s/\n//g;

	#################################################################
	# We now copy/rename the temporary file depending on where	#
	# the file exists currently.					#
	#################################################################

	if(dirname($args{temporary_file}) eq $args{docrepos}) {
		if(!rename($args{temporary_file},"$args{docrepos}/$local_name")) {
			return -6;
		}
	} else {
		# Files in different directories so copy it...	
		my ($rfd,$wfd,$b,$b2);
		open($rfd,$args{temporary_file}) or return -5;
		open($wfd,">$args{docrepos}/$mm") or return -5;
		while($b=sysread($rfd,$buffer,1024)) {
			$b2=syswrite($wfd,$buffer,$b);
			if($b2!=$b) {
				close($rfd);
				close($wfd);
				unlink("$args{docrepos}/$local_name");
				return -6;
			}
		}
		close($rfd);
		close($wfd);
	}

	#########################################################
	# Actual file has been written, so now we need to set	#
	# permissions on the file...				#
	#########################################################

	chmod 0600,"$args{docrepos}/$local_name";

	$s="INSERT INTO doc_index (description,original_name,local_name,content_sha1,uploaded_time,sections,uploader) VALUES ";
	my $t=time;
	$s.="('$description','$args{original_name}','$local_name','$args{content_sha1}','$t','$args{sections}','$uploader');";
	my $r=$self->do($s);
	if(! $r) {
		return -3;
	}
	return (1,$local_name);
}

sub get_olocal_name_via_clocal_name {
my $self=shift;
my %args=@_;

	if(!exists($args{clocal_name})) {
		return (-2,"Missing mandatory 'clocal_name' parameter.");
	}
	my $s="SELECT * FROM version_doc_index WHERE clocal_name = '$args{clocal_name}';";
	my $res=$self->get_rows($s);
	if(! @$res || !defined($res->[0]->{olocal_name})) {
		return(0,0);
	}
	return(1,$res->[0]->{olocal_name});
}

sub get_docs {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return (-2,[],-1);
	}
	my $section=$args{section};
	if(!exists($args{order})) {
		return (-2,[],-1);
	}
	my $order=$args{order};
	if(!exists($args{page})) {
		return (-2,[],-1);
	}
	my $page=$args{page};

	my $s="SELECT * FROM doc_index WHERE sections = '$section' OR sections LIKE '$section,%' OR sections LIKE '%,$section' OR sections LIKE '%,$section,%';";
	my $res=$self->get_rows($s);
	if(!@$res) {
		return(0,[],-1);
	}

	# We should take the details now and sort them ...

	my $page_size=20;
	if(exists($args{pagesize}) && $args{pagesize}>0 && $args{pagesize}<100) {
		$page_size=$args{pagesize};
	}
	my $first=($page-1)*$page_size;
	my $next=($page)*$page_size;
	if(@$res<=$first) {
		return(0,[],scalar(@$res));
	}

	# So enough results to return some ...
	if(@$res>=$next) {
		return(1,[@$res[$first .. $next-1]],scalar(@$res));
	}
	return(1,[@$res[$first .. scalar(@$res)-1]],scalar(@$res));
}

# This will return the following information about the section in question...

# 1 - the number of documents viewable in section
# 2 - the total size impact to the repository
# 3 - the time of the latest upload to the section

sub get_section_stats {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return (-2,-1,-1,-1);
	}
	my $section=$args{section};
	if(!exists($args{docrepos})) {
		return (-2,-1,-1,-1);
	}
	my $docrepos=$args{docrepos};

	my $s="SELECT local_name,uploaded_time FROM doc_index WHERE sections = '$section' OR sections LIKE '$section,%' OR sections LIKE '%,$section,%' OR sections LIKE '%,$section';";

	my $res=$self->get_rows($s);
	if(!@$res || !defined($res->[0]->{local_name})) {
		return(1,0,0,0);
	}

	my $n2=0;
	my $n=0;
	my $tsize=0;
	my @S;
	for my $crec (@$res) {
		next if !defined($crec->{local_name});
		if(-f "$docrepos/$crec->{local_name}") {
			$n2++;
			@S=stat(_);
			$tsize+=$S[7];
			$n=$crec->{uploaded_time} if $crec->{uploaded_time}>$n;
		}
	}
	return(1,$n2,$tsize,$n);
}

sub get_doclist_unique_to_section {
my $self=shift;
my %args=@_;

	if(!exists($args{section})) {
		return (-2,"Missing the 'section' parameter.");
	}
	my $section=$args{section};
	my $s="SELECT * FROM doc_index WHERE sections = '$section';"; # only docs in this section only
	my $res=$self->get_rows($s);
	if(!defined($res)) {
		return(-3,"Error running query: $s");
	}
	my @A=();
	for my $crec (@$res) {
		if(defined($crec->{local_name})) {
			push @A,"$crec->{content_sha1}|$crec->{local_name}";
		}
	}
	return(1,[@A]);
}

sub get_document_list {
my $self=shift;
my %args=@_;

	if(!exists($args{user})) {
		return (-2,"Missing the 'user' parameter.");
	}
	my ($r1,$r2)=get_user_sections($self,user => $args{user}, type=>"reader");
	if($r1!=1) {
		return(-3,"Error RC=$r1 running 'get_user_sections'.");
	}
	# We now perform a scan against all documents, getting the sha1/lname keys of
	# each...

	my %main_keys=();
	for my $section (@$r2) {
		my $s="SELECT * FROM doc_index WHERE sections = '$section' OR sections LIKE '$section,%' OR sections LIKE '%,$section,%' OR sections LIKE '%,$section';";
		my $res=$self->get_rows($s);
		for my $crec (@$res) {
			if(defined($crec->{local_name})) {
				$main_keys{"$crec->{content_sha1}|$crec->{local_name}"}={%$crec};
			}
		}
	}

	# main_keys contains the hash details of all suitable matches, so return
	# the information required for the request.

	return(1,{%main_keys});
}

#########################################################################
# The history argument is optional and should be set to 1 or 0. 	#
# If a documnet has history it does the following:			#
# 0 - The latest version in history is promoted from history to the	#
#     the document repository.						#
# 1 - All history is deleted for the document too [easier to code!]	#
#########################################################################

sub remove_doc {
my $self=shift;
my %args=@_;

	my $history=0;
	if(!exists($args{sha1})) {
		return (-2,"Missing the 'sha1' parameter.");
	}
	if(!exists($args{lname})) {
		return (-2,"Missing the 'lname' parameter.");
	}
	if(exists($args{history})) {
		$history=$args{history};
	}
	my $lname=$args{lname};
	my $sha1=$args{sha1};

	my $s="SELECT * FROM doc_index WHERE content_sha1 = '$args{sha1}' AND local_name = '$args{lname}';";
	my $res=$self->get_rows($s);
	if(!@$res || !defined($res->[0]->{local_name})) {
		return(-1,"Document $args{sha1}/$args{lname} does not exist.");
	}

	#########################################################################
	# $res contains all the details ... so if we are not deleting		#
	# history we need those details to promote the previous doc, if any.	#
	#########################################################################
	if(! $self->do("BEGIN TRANSACTION")) {
		print STDERR "Unable to begin transaction!\n";
	}

	$s="SELECT * FROM version_doc_index WHERE clocal_name = '$res->[0]->{local_name}' ORDER BY version DESC;";
	my $hr=$self->get_rows($s);
	my $has_history=0;
	if(defined($hr) && defined($hr->[0]->{local_name})) {
		$has_history=1;
	}

	#################################################################
	# The document exists, so we've got to delete it.		#
	#################################################################

	my $r=$self->do("DELETE FROM doc_index WHERE content_sha1 = '$args{sha1}' AND local_name = '$args{lname}';");
	if(!$r) {
		$self->do("END TRANSACTION;");
		return (-3,"An SQL error has occurred.");
	}

	#################################################################
	# If the has_history == 0 then we can exit now.			#
	#################################################################
	
	if($has_history==0) {
		$self->do("END TRANSACTION;");
		return(1,undef);
	}

	#################################################################
	# Handle the situation where all history is being deleted.	#
	#################################################################
	my @dels=();
	if($history==1) {
		push @dels,$_->{local_name} foreach(@$hr);
		my $r=$self->do("DELETE FROM version_doc_index WHERE clocal_name = '$res->[0]->{local_name}';");
		if(!$r) {
			print STDERR "Failed to delete from version_doc_index - rollback!\n";
			$self->do("ROLLBACK");
			return (-3,"An SQL error has occurred whilst attempting to remove document history.");
		}
		$self->do("END TRANSACTION;");
		return(1,[@dels]);
	}

	#################################################################
	# Ok - the more complex requirement - we need to 'promote' the	#
	# highest level document...					#
	# 1st step: Adding a new doc_index entry ...			#
	#################################################################

	my $history_count=scalar(@$hr);
	$s="INSERT INTO doc_index (description,original_name,local_name,content_sha1,uploaded_time,sections) VALUES ('$res->[0]->{description}','$hr->[0]->{original_name}','$hr->[0]->{local_name}','$hr->[0]->{content_sha1}','$hr->[0]->{uploaded_time}','$res->[0]->{sections}');";
	if(! $self->do($s)) {
		print STDERR "Failed to insert into doc_index - rollback!\n";
		$self->do("ROLLBACK");
		return (-3,"An SQL error has occurred whilst attempting to add entry to doc_index.");
	}

	#################################################################
	# 2nd point : delete the existing doc_index entry.		#
	#################################################################
	$s="DELETE FROM doc_index WHERE content_sha1 = '$args{sha1}' AND local_name = '$args{lname}';";
	if(! $self->do($s)) {
		print STDERR "Failed to delete from doc_index - rollback!\n";
		$self->do("ROLLBACK");
		return (-3,"An SQL error has occurred whilst attempting to delete entry from doc_index.");
	}

	#################################################################
	# Now we need to manipulate the history - this depends on the	#
	# the actual number of history entries that are present.	#
	# If just 1 then it can be deleted, so do that first...		#
	#################################################################
	
	if($history_count==1) {
		$s="DELETE FROM version_doc_index WHERE clocal_name = '$res->[0]->{local_name}';";
		if(! $self->do($s)) {
			print STDERR "Failed to delete from vesion_doc_index - rollback!\n";
			$self->do("ROLLBACK");
			return (-3,"An SQL error has occurred whilst attempting to delete entry from version_doc_index.");
		}
	}

	#################################################################
	# If multiple documents are present we've got to delete the	#
	# first only, and then update the rest to make sure that	#
	# clocal_name is set to $hr->[0]->{local_name}			#
	#################################################################

	$s="DELETE FROM version_doc_index WHERE local_name = '$hr->[0]->{local_name}';";
	if(! $self->do($s)) {
		print STDERR "Failed to delete from vesion_doc_index - rollback!\n";
		$self->do("ROLLBACK");
		return (-3,"An SQL error has occurred whilst attempting to delete entry from version_doc_index.");
	}
	$s="UPDATE version_doc_index SET clocal_name = '$hr->[0]->{local_name}' WHERE clocal_name = '$res->[0]->{local_name}';";
	if(! $self->do($s)) {
		print STDERR "Failed to update vesion_doc_index - rollback!\n";
		$self->do("ROLLBACK");
		return (-3,"An SQL error has occurred whilst attempting to update entries in version_doc_index.");
	}

	$self->do("END TRANSACTION;");
	return(1,[@dels]);
}

sub get_versions {
my $self=shift;
my %args=@_;

	if(!exists($args{lname})) {
		return (-2,"Missing the 'lname' parameter.",[]);
	}

	# Get details of any records and return them
	my $s="SELECT * FROM version_doc_index WHERE clocal_name = '$args{lname}' ORDER BY version DESC;";
	$res=$self->get_rows($s);
	if(!defined($res) || !defined($res->[0]->{clocal_name})) {
		return (1,0,[]);
	}
	return(1,scalar(@$res),[@$res]);
}

# Returns the total number of document repository sections defined...

sub get_section_count {
my $self=shift;

	my $s="SELECT COUNT(*) AS count FROM sections";
	$res=$self->get_rows($s);
	if(!defined($res) || !defined($res->[0]->{count})) {
		return 0;
	}
	return $res->[0]->{count};
}

sub get_group_count {
my $self=shift;

	my $s="SELECT COUNT(*) AS count FROM group_info";
	$res=$self->get_rows($s);
	if(!defined($res) || !defined($res->[0]->{count})) {
		return 0;
	}
	return $res->[0]->{count};
}

sub move_to_history {
my $self=shift;
my %args=@_;

	if(!exists($args{sha1})) {
		return (-2,"Missing the 'sha1' parameter.");
	}
	if(!exists($args{lname})) {
		return (-2,"Missing the 'lname' parameter.");
	}
	if(!exists($args{clname})) {
		return (-2,"Missing the 'clname' parameter.");
	}
	if(!exists($args{olname})) {
		return (-2,"Missing the 'olname' parameter.");
	}

	#################################################################
	# Open the database and if olname is not NONE then we		#
	# must check that it exists, and abort if it does not.		#
	#################################################################

	if($args{olname} ne "NONE") {
		my $s="SELECT * FROM version_doc_index WHERE local_name = '$args{olname}';";
		my $res=$self->get_rows($s);
		if(!@$res || !defined($res->[0]->{local_name})) {
			return(-4,"Original local name not specified is not found.");
		}
	}

	#################################################################
	# We must also check to see if the existing file actually	#
	# exists and abort if not.					#
	#################################################################

	my $s="SELECT * FROM doc_index WHERE content_sha1 = '$args{sha1}' AND local_name = '$args{lname}';";
	my $res2=$self->get_rows($s);
	if(!@$res2 || !defined($res2->[0]->{local_name})) {
		return(-5,"Document $args{sha1}/$args{lname} does not exist.");
	}

	#################################################################
	# Ok the current [and if necessary] the original document	#
	# exist, so perform the processing. First we add a history	#
	# record and then delete the original.				#
	#################################################################

	my $version=1;
	my $last_local="";
	if($args{olname} ne "NONE") {
		my $s="SELECT MAX(version) AS version,MAX(local_name) AS mlocal FROM version_doc_index WHERE olocal_name = '$args{olname}';";
		my $res=$self->get_rows($s);
		if(@$res && defined($res->[0]->{version})) {
			$version=1+int($res->[0]->{version});
		}
		if(@$res && defined($res->[0]->{mlocal})) {
			$last_local=$res->[0]->{mlocal};
		}
	}

	#################################################################
	# so version is the version number to use for the new 		#
	# history record. If there are other older versions then we 	#
	# need to change all records to set clocal_name to lname.	#
	#################################################################

	if($args{olname} ne "NONE") {
		my $s="UPDATE version_doc_index SET clocal_name = '$args{clname}' WHERE olocal_name = '$args{olname}';";
		my $res=$self->do($s);
		if(!$res) {
			# The update failed, so exit.
			return(-4,"Failed to update existing version_doc_index entries.");
		}
	} else {
		$args{olname}=$args{lname};
	}

	# Updated the existing records, so add the new record to the	#
	# history
	# original_name = res2->[0]->{original_name}
	# clocal_name = lname
	# local_name = lname
	# olocal_name = $args{olname};
	# contents_sha1 = $args{sha1};
	# uploaded_time = $res2->[0]->{uploaded_time}
	# version = $version

	{
		my $s="INSERT INTO version_doc_index (original_name,clocal_name,local_name,olocal_name,content_sha1,uploaded_time,version) VALUES ('$res2->[0]->{original_name}','$args{clname}','$args{lname}','$args{olname}','$args{sha1}','$res2->[0]->{uploaded_time}','$version')";
		my $res=$self->do($s);
		if(!$res) {
			$s="UPDATE version_doc_index SET clocal_name = '$last_local' WHERE olocal_name = '$args{olname}';";
			$res=$self->do($s);
			# The insert failed, so we must revert the previous update
			return(-4,"Failed to insert new record into version_doc_index table.");
		}
	}

	#################################################################
	# Added the entry to the history table, and updated existing 	#
	# records, so now delete the entry from the doc_index table.	#
	#################################################################

	{
		my $s="DELETE FROM doc_index WHERE local_name = '$args{lname}' OR content_sha1 = '$args{sha1}';";
		my $res=$self->do($s);
		if(!$res) {
			$s="DELETE_FROM version_doc_index WHERE local_name = '$args{lname}' AND content_sha1 = '$args{sha1}';";
			$res=$self->do($s);
			$s="UPDATE version_doc_index SET clocal_name = '$last_local' WHERE olocal_name = '$args{olname}';";
			$res=$self->do($s);
		}
	}
	return(1,$version);
}

sub has_history {
my $self=shift;
my %args=@_;

	my @W=qw(lname);
	for my $i (@W) {
		if(!exists($args{$i}) || !defined($args{$i})) {
			return -2;
		}
	}

	my $s="SELECT COUNT(*) AS c FROM version_doc_index WHERE clocal_name = '$args{lname}';";
	$res=$self->get_rows($s);
	if(!defined($res) || !defined($res->[0]->{c})) {
		return -1;
	}
	return int($res->[0]->{c});
}

sub upload_to_lname {
my $self=shift;
my %args=@_;

	my @W=qw(docrepos temporary_file);
	for my $i (@W) {
		if(!exists($args{$i}) || !defined($args{$i})) {
			return (-2,"Missing argument '$i'.");
		}
	}
	my $s="SELECT max(local_name) AS mm FROM doc_index";
	$res=$self->get_rows($s);
	my $mm;
	if(! @$res || !defined($res->[0]->{mm})) {
		$mm=1;
	} else {
		$mm=$res->[0]->{mm};
		$mm =~ s/^0+//;
		$mm=int($mm)+1;
	}
	my $local_name=sprintf("%06d",$mm);

	#################################################################
	# We now copy/rename the temporary file depending on where	#
	# the file exists currently.					#
	#################################################################

	if(dirname($args{temporary_file}) eq $args{docrepos}) {
		if(!rename($args{temporary_file},"$args{docrepos}/$local_name")) {
			return -6;
		}
	} else {
		# Files in different directories so copy it...	
		my ($rfd,$wfd,$b,$b2);
		open($rfd,$args{temporary_file}) or return -5;
		open($wfd,">$args{docrepos}/$mm") or return -5;
		while($b=sysread($rfd,$buffer,1024)) {
			$b2=syswrite($wfd,$buffer,$b);
			if($b2!=$b) {
				close($rfd);
				close($wfd);
				unlink("$args{docrepos}/$local_name");
				return (-6,"Ran out of disk space writing to '$args{docrepos}/$mm'.");
			}
		}
		close($rfd);
		close($wfd);
	}

	chmod 0600,"$args{docrepos}/$local_name";
	return (1,$local_name);
}

sub _contains_field($$$) {
my ($dbh,$obj,$field)=@_;
my ($cmd,$rc,$sth);

	$cmd="SELECT sql FROM SQLITE_MASTER WHERE type = 'table' AND name = '$obj';";
	my $h=$dbh->selectrow_hashref("$cmd");
	if(!defined($h)) {
		return -1; # table does not exist
	}
	my @F;
	if ($h->{sql} =~ /\n/) {
		@F=split(/\n/,$h->{sql});
	} else {
		my $xx=$h->{sql};
		$xx =~ s/^CREATE TABLE $obj\(//;
		$xx =~ s/\)$//;
		@F=("junk",split(/,/,$xx),"junk");
	}
	for my $cl (@F[1 .. $#F-1]) {
		my $y=$cl;
		$y =~ s/^\s+//;
		my @F2=split(/\s+/,$y);
		if(lc($F2[0]) eq lc($field)) {
			return 1; # field already exists
		}
	}
	return 0; # field does not exist
}

sub _table_field_list($$) {
my ($dbh,$table)=@_;
my ($cmd,$rc,$sth);

	$cmd="SELECT sql FROM SQLITE_MASTER WHERE type = 'table' AND name = '$table';";
	my $h=$dbh->selectrow_hashref("$cmd");
	if(!defined($h)) {
		return (0,[],[]); # table does not exist
	}
	my @F;
	if ($h->{sql} =~ /\n/) {
		@F=split(/\n/,$h->{sql});
	} else {
		my $xx=$h->{sql};
		$xx =~ s/^CREATE TABLE $table\(//;
		$xx =~ s/\)$//;
		@F=("junk",split(/,/,$xx),"junk");
	}
	my @FF=();
	my @FF2=();
	for my $cl (@F[1 .. $#F-1]) {
		my $y=$cl;
		$y =~ s/^\s+//;
		$y =~ s/,$//;
		my @YY=split(/\s+/,$y);
		push @FF,$YY[0];
		push @FF2,$YY[1];
	}
	return(1,[@FF],[@FF2]);
}

sub _get_rows($$) {
my ($dbh,$query)=@_;
my ($sth,$rc,@R,$aref);

	$sth=$dbh->prepare($query);
	if(!defined($sth)) {
		$rc=$dbh->errstr;
		return undef;
	}
	$sth->execute();
	@R=();
	while($aref  = $sth->fetchrow_hashref) {
		push @R,{%$aref};
	}
	return \@R;
}

# Returns:								#
# -1 = specified table does not exist.					#
# -2 = unable to get field definitions for table.			#
# 1  = field already present; no work required.				#
# 10+ - field added, r-10 records updated.				#

sub _add_field($$$$$) {
my ($dbh,$table,$field,$type,$value)=@_;

	my $z=_contains_field($dbh,$table,$field);
	if($z!=0) {
		return $z;
	}
	# Get the table defintiion information..

	my ($x,$r1,$r2)=_table_field_list($dbh,$table);
	if($x==0) {
		return -2;
	}
	my $cmd="create table ${table}_backup(\n";
	my $cmd2="create table ${table}(\n";
	my $ii=0;
	while($ii<@$r1) {
		$r2->[$ii]="" if !defined($r2->[$ii]);
		$cmd2.="  $r1->[$ii] $r2->[$ii],\n";
		if($ii+1==@$r1) {
			$cmd.="  $r1->[$ii] $r2->[$ii]\n";
		} else {
			$cmd.="  $r1->[$ii] $r2->[$ii],\n";
		}
		$ii++;
	}
	$cmd.=");";
	$cmd2.="  $field $type\n);";

	$dbh->do($cmd);
	$cmd="insert into ${table}_backup SELECT " . join(",",@$r1) . " FROM $table;";
	$dbh->do($cmd);
	$cmd="DROP TABLE $table;";
	$dbh->do($cmd);
	$dbh->do($cmd2);
	# Read recrods from backup to new...
	$cmd="select * from ${table}_backup;";
	my @FLIST=(@$r1,$field);
	my $flist_txt=join(",",@FLIST);
	my $rr=_get_rows($dbh,$cmd);
	my $count=0;
	for my $crec (@$rr) {
		$crec->{$field}=$value;
		$cmd="INSERT INTO $table ($flist_txt) VALUES (";
		my $cc="";
		for my $xxx (@FLIST) {
			$cc.=",'$crec->{$xxx}'";
		}
		$cmd.=substr($cc,1) . ");";
		$dbh->do($cmd);
		$count++;
	}
	$cmd="DROP TABLE ${table}_backup;";
	$dbh->do($cmd);
	return 10+$count;
}

1;

# vim: ts=8:sw=8
